﻿Imports System.Data.SqlClient
Imports System.Configuration
Public Class DataProvider
    Public Shared Function ConnectionData() As SqlConnection
        ' Dim str As String = "Data Source=.\SQLEXPRESS;Initial Catalog=QLPHONGTRO;Persist Security Info=True;User ID=sa; password = 123;"
        Dim str As String = ConfigurationManager.ConnectionStrings("DA_QL_PhongTro.My.MySettings.QLPHONGTROConnectionString").ConnectionString
        Dim cn As SqlConnection
        cn = New SqlConnection(str)
        Try
            cn.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try
        Return cn
    End Function
    Public Shared Function LoadTable(ByVal sql As String) As DataTable
        Dim cn As SqlConnection = DataProvider.ConnectionData()
        Dim da As New SqlDataAdapter(sql, cn)
        Dim dt As New DataTable()
        Try
            da.Fill(dt)
        Catch ex As Exception
            MessageBox.show(ex.Message.ToString)
        End Try
        Return dt
    End Function
    Public Shared Function LoadTablePara(ByVal sql As String, ByVal name As String(), ByVal value As Object(), ByVal Nparameter As Integer) As DataTable
        Dim cn As SqlConnection = DataProvider.ConnectionData()
        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To Nparameter - 1

            cmd.Parameters.AddWithValue(name(i), value(i))
        Next
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        Return dt
    End Function
    Public Shared Sub ExecuteNonQuery(ByVal sql As String)
        Dim cn As SqlConnection = DataProvider.ConnectionData()
        Dim cmd As New SqlCommand(sql, cn)
        Try
            cmd.ExecuteNonQuery()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        cn.Close()
    End Sub
    Public Shared Function ExecuteScalar(ByVal sql As String) As Object
        Dim value As Object = Nothing
        Dim cn As SqlConnection = DataProvider.ConnectionData()
        Dim cmd As New SqlCommand(sql, cn)
        Try
            value = cmd.ExecuteScalar()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        cn.Close()
        Return value
    End Function
    Public Shared Sub ExecuteNonQueryPara(ByVal sql As String, ByVal name As String(), ByVal value As Object(), ByVal Nparameter As Integer)
        Dim cn As SqlConnection = DataProvider.ConnectionData()
        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To Nparameter - 1
            cmd.Parameters.AddWithValue(name(i), value(i))
        Next
        Try
            cmd.ExecuteNonQuery()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
        cn.Close()
    End Sub

End Class
